using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace DoAn
{
    class QLHS
    {
        private OleDbConnection con = new OleDbConnection();
        public QLHS()
        {
            string strKN = null;
            strKN = "provider=microsoft.jet.oledb.4.0; data source=QLDiemmdb";
            con.ConnectionString = strKN;
            if (con.State == ConnectionState.Closed)
                con.Open();
        }

        public DataSet GetSinhVien()
        {
            DataSet ds = new DataSet();

            OleDbDataAdapter daLop = new OleDbDataAdapter("select * from lop", con);
            OleDbDataAdapter daSV = new OleDbDataAdapter("select * from sinhvien", con);
            daLop.Fill(ds, "lop");
            daSV.Fill(ds, "sinhvien");

            DataColumn cotcha = new DataColumn();
            DataColumn cotcon = new DataColumn();

            cotcha = ds.Tables["lop"].Columns["malop"];
            cotcon = ds.Tables["sinhvien"].Columns["malop"];

            ds.Relations.Add("LopSinhVien", cotcha, cotcon);
            return ds;
        }
        public DataSet GetDataSetMotTable(string strTenBang)
        {
                OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            string sql = null;
            sql = "select * from " + strTenBang;
            cmd.Connection =con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            da.SelectCommand = cmd;
            da.Fill(ds, strTenBang);
            return ds;
        }

        public DataSet GetDataSet(string strSQL)
        {
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da= new OleDbDataAdapter();
            DataSet ds = new DataSet();

            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL;
            da.SelectCommand = cmd;
            da.Fill(ds);

            return ds;
        }

        public DataSet GetAllTable()
        {
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            string strSQL;
            //sinh vien
            strSQL = "select * from sinhvien";
            cmd.CommandText = strSQL;
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            da.SelectCommand = cmd;
            da.Fill(ds, "sinh vien");

            //lop
            strSQL = "select * from lop";
            da = new OleDbDataAdapter(strSQL, con);
            da.Fill(ds, "lop");

            //mon hoc
            strSQL = "select * from monhoc";
            da = new OleDbDataAdapter(strSQL, con);
            da.Fill(ds, "mon hoc");

            //khoa
            strSQL = "select * from khoa";
            da = new OleDbDataAdapter(strSQL, con);
            da.Fill(ds, "khoa");

            //diem
            strSQL = "select * from diem";
            da = new OleDbDataAdapter(strSQL, con);
            da.Fill(ds, "diem");

            return ds;
        }

        public  DataSet LayDataSetSinhVienLopQuanHe()
        {
            string sql;
            DataSet ds = new DataSet();
            OleDbDataAdapter daSV = new OleDbDataAdapter("select * from sinhvien", con);
            OleDbDataAdapter daLop = new OleDbDataAdapter("select * from lop", con);

            daLop.Fill(ds, "lop");
            daSV.Fill(ds, "sinhvien");

            DataColumn cotcha = new DataColumn();
            DataColumn cotcon = new DataColumn();
            cotcha = ds.Tables["lop"].Columns["malop"];
            cotcon = ds.Tables["sinhvien"].Columns["malop"];
            ds.Relations.Add("lopsinhvien", cotcha, cotcon);

            return ds;
        }

        public DataSet SinhVien_Diem()
        {
                string strSQL;
            DataSet ds = new DataSet();
            OleDbDataAdapter daSV = new OleDbDataAdapter("select *  from sinhvien", con);

            strSQL = "select masv, diem, lanthi, tenmh, sotc from diem d, monhoc m where d.mamh=m.mamh";
            OleDbDataAdapter daDiem = new OleDbDataAdapter(strSQL, con);
            
            daSV.Fill(ds, "sinhvien");
            daDiem.Fill(ds, "diem");

            DataColumn  cotcha = new DataColumn();
            DataColumn cotcon = new DataColumn();
            cotcha = ds.Tables["sinhvien"].Columns["masv"];
            cotcon = ds.Tables["diem"].Columns["masv"];
            ds.Relations.Add("sinhviendiem", cotcha, cotcon);

            return ds;
        }
    }
}
